#region [ References ]
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using System.Threading;
using System.Data.SqlClient;
using System.Net.NetworkInformation;
#endregion

namespace LittleUmph
{
    #region [ ErrorType Enum ]
    /// <summary>
    /// NoError - no error
    /// InputError - parameter error, invalid columnName, function input error
    /// QueryError - sql syntax, invalid command
    /// SpError - store procedure result error
    /// ConnectionError - Can not connect
    /// LibraryError - error generated by QuickDB functions
    /// Generic - general exceptions and all other errors
    /// </summary>
    public enum ErrorType {
        /// <summary>
        /// NoError - no error
        /// </summary>
        NoError, 

        /// <summary>
        /// InputError - parameter error, invalid columnName, function input error
        /// </summary>
        InputError, 

        /// <summary>
        /// QueryError - sql syntax, invalid command (this includes InputErrors)
        /// </summary>
        QueryError, 

        /// <summary>
        /// SPError - store procedure result error (this includes InputErrors, QueryError)
        /// </summary>
        SpError, 

        /// <summary>
        /// ConnectionError - Can not connect to the database or network error (this includes InputErrors, QueryError, SPError)
        /// </summary>
        ConnectionError, 

        /// <summary>
        /// LibraryError - error generated by QuickDB functions (this includes InputErrors, QueryError, SPError, ConnectionError).
        /// </summary>
        LibraryError, 

        /// <summary>
        /// Generic - general exceptions and all other errors
        /// </summary>
        GenericError 
    };
    #endregion

    #region [ DBErrorEventArgs ]
    /// <summary>
    /// Database error event argument.
    /// </summary>
    public class DbErrorEventArgs : EventArgs
    {
        private readonly Exception _Exception;
        private readonly ErrorType _ErrorType;

        /// <summary>
        /// Gets the last exception.
        /// </summary>
        /// <value>The last exception.</value>
        public Exception Exception
        {
            get { return _Exception; }
        }

        /// <summary>
        /// Gets the last type of the error.
        /// </summary>
        /// <value>The last type of the error.</value>
        public ErrorType ErrorType
        {
            get { return _ErrorType; }
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="DbErrorEventArgs"/> class.
        /// </summary>
        /// <param name="exception">The exception.</param>
        /// <param name="errorType">Type of the error.</param>
        public DbErrorEventArgs(Exception exception, ErrorType errorType)
        {
            _Exception = exception;
            _ErrorType = errorType;
        }
    }
    #endregion

    /// <summary>
    /// Provide a quick access to the database without 
    /// encurring much of the overhead (less typing, less error checking)
    /// </summary>
    public class QuickDb
    {
        #region [ Constants ]
        /// <summary>
        /// Non executing query error. The "NonQuery()" functions return this value to indicate an error.
        /// </summary>       
        /// <remarks>nquery(SqlCommand)</remarks>
        public const int INT_NonQueryError = -2;
        #endregion

        #region [ Private Variables ]
        private string      _name = "";
        private ErrorType   _showErrorByType = ErrorType.NoError;
        private string      _connectionString;
        private Exception   _lastException;
        private ErrorType   _lastErrorType;
        private int         _commandTimeout = 30;
        private bool        _logError = true;
        #endregion

        #region [ Properties ]
        /// <summary>
        /// Use for the purpose of distinguishing between multiple databases
        /// </summary>
        public string Name
        {
            get { return _name; }
            set { _name = value; }
        }

        /// <summary>
        /// Display error messages [debug purposes ONLY] (default value of "NoError")
        /// Error Level is progressive (NoError, InputError, QueryError, SPError, ConnectionError, LibraryError, GenericError)
        /// Example: if you specify ConnectionError filter, it will include InputError, QueryError ... (but NOT include Library and GenericError)
        /// </summary>
        public ErrorType ShowErrorByType
        {
            get { return _showErrorByType; }
            set { _showErrorByType = value; }
        }

        /// <summary>
        /// Connection string.
        /// </summary>
        public string ConnectionString
        {
            get { return _connectionString; }
            set { _connectionString = value; }
        }

        /// <summary>
        /// Contains the last exception.
        /// </summary>
        public Exception LastExeception
        {
            get { return _lastException; }
            private set { _lastException = value; }
        }

        /// <summary>
        /// Indicate the type of of error associated with the <see cref="LastExeception"/>
        /// </summary>
        public ErrorType LastErrorType
        {
            get { return _lastErrorType; }
            private set { _lastErrorType = value; }
        }

        /// <summary>
        /// How long the SQLCommand waits for query to process (default 30 seconds).
        /// This is the query command execution timeout NOT the connection timeout.
        /// </summary>
        public int CommandTimeout
        {
            get { return _commandTimeout; }
            set { _commandTimeout = value; }
        }
        #endregion

        #region [ Constructor ]
        /// <summary>
        /// Create QuickDB object using the connnection string and init the database after 1 second if the autoCreatePool is set.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        public QuickDb(string connectionString)
        {
            ConnectionString = connectionString;

            ////Todo: change this
            ChronoTask.Run(delegate() { CanCreateConnection(); }, 200, TimerType.SystemTimer);
        }
        #endregion

        #region [ Error Handler ]
        /// <summary>
        /// Display the exception error
        /// </summary>
        /// <param name="xpt">The Exception.</param>
        /// <param name="errorType">Type of the error.</param>
        private void handleError(Exception xpt, ErrorType errorType)
        {
            try
            {
                _lastException = xpt;
                _lastErrorType = errorType;

                if (errorType <= ShowErrorByType)
                {
                    MessageBox.Show(xpt.Message, xpt.Source, MessageBoxButtons.OK, MessageBoxIcon.Error);
                }

                if (onDbErrorOccurred(_lastException, _lastErrorType))
                {
                    return;
                }
            }
            catch (Exception) { }
        }
        #endregion

        #region [ Common Functions: GetDate ]
        /// <summary>
        /// Get the database server date and time. 
        /// Careful, if the server and the computer 
        /// is on a different timezone your time will be wrong.
        /// To be sure use GetDbDateTimeUtc() instead.
        /// </summary>
        /// <returns>Return MinDate on error</returns>
        public DateTime GetDbDateTime()
        {            
            const string sqlQuery = "SELECT GETDATE()";
            return ScalarQuery<DateTime>(sqlQuery, 0, DateTime.MinValue);
        }

        /// <summary>
        /// Get the database server date and UTC time 
        /// </summary>
        /// <returns>Return MinDate on error</returns>
        public DateTime GetDbDateTimeUtc()
        {
            const string sqlQuery = "SELECT GETUTCDATE()";
            return ScalarQuery<DateTime>(sqlQuery, 0, DateTime.MinValue);
        }
        #endregion

        #region [ Static DB Functions: Dispose ]
        /// <summary>
        /// Close and dispose data reader.
        /// </summary>
        /// <param name="dr">The data reader instance (Read() already been called).</param>
        public static void DisposeDr(SqlDataReader dr)
        {
            if (dr != null)
            {
                try
                {
                    dr.Dispose();
                }
                catch (Exception) { }
            }
        }

        /// <summary>
        /// Close and dispose the database connection
        /// </summary>
        public static void DisposeDbConnection(SqlConnection connection)
        {
            if (connection != null)
            {
                try
                {
                    connection.Dispose();
                }
                catch (Exception) {}
            }
        }
        #endregion

        #region [ Static DataReader Functions ]
        /// <summary>
        /// Check if the columnName contains null
        /// </summary>
        /// <param name="dr">The data reader instance (Read() already been called).</param>
        /// <param name="columnName">Name of the column.</param>
        /// <returns></returns>
        public static bool DrIsNull(SqlDataReader dr, string columnName)
        {
            int index = dr.GetOrdinal(columnName);
            if (dr.IsDBNull(index))
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// Check to make sure the columnName is NOT null.
        /// </summary>
        /// <param name="dr">The data reader instance (Read() already been called).</param>
        /// <param name="columnName">The column name.</param>
        /// <returns></returns>
        public static bool DrIsNotNull(SqlDataReader dr, string columnName)
        {
            return !DrIsNull(dr, columnName);
        }

        /// <summary>
        /// Check if the columnName is null or a string that contains only white space
        /// </summary>
        /// <param name="dr">The data reader instance (Read() already been called).</param>
        /// <param name="columnName">The column name.</param>
        /// <returns></returns>
        public static bool DrIsNullOrEmptyString(SqlDataReader dr, string columnName)
        {
            int index = dr.GetOrdinal(columnName);
            if (dr.IsDBNull(index))
            {
                return true;
            }
            return String.IsNullOrEmpty(dr[index].ToString());
        }

        /// <summary>
        /// Check if the columnName is null or an int32 that is not zero
        /// </summary>
        /// <param name="dr">The data reader instance (Read() already been called).</param>
        /// <param name="columnName">The column name.</param>
        /// <returns></returns>
        public static bool DrIsNullOrZero(SqlDataReader dr, string columnName)
        {
            int index = dr.GetOrdinal(columnName);
            if (dr.IsDBNull(index))
            {
                return true;
            }
            return dr.GetInt32(index) == 0;
        }

        /// <summary>
        /// Get string from dr (dr must called Read() function)
        /// </summary>
        /// <param name="dr">The data reader instance (Read() already been called).</param>
        /// <param name="columnName">The column name.</param>
        /// <param name="valueOnError">on null return this value</param>
        /// <returns></returns>
        public static string DrGetString(SqlDataReader dr, string columnName, string valueOnError)
        {
            int index = dr.GetOrdinal(columnName);
            if (dr.IsDBNull(index))
            {
                return valueOnError;
            }
            return dr[index].ToString();
        }

        /// <summary>
        /// Get int from dr (dr must called Read() function)
        /// </summary>
        /// <param name="dr">The data reader instance (Read() already been called).</param>
        /// <param name="columnName">The column name.</param>
        /// <param name="valueOnError">on null return this value</param>
        /// <returns></returns>
        public static int DrGetInt32(SqlDataReader dr, string columnName, int valueOnError)
        {
            int index = dr.GetOrdinal(columnName);
            return dr.IsDBNull(index) ? valueOnError : dr.GetInt32(index);
        }

        /// <summary>
        /// Get int from dr.
        /// </summary>
        /// <param name="dr">The data reader instance (Read() already been called).</param>
        /// <param name="columnName">The column name.</param>
        /// <param name="valueOnError">on null return this value</param>
        /// <returns></returns>
        public static int DrGetInt16(SqlDataReader dr, string columnName, int valueOnError)
        {
            int index = dr.GetOrdinal(columnName);
            return dr.IsDBNull(index) ? valueOnError : dr.GetInt16(index);
        }

        /// <summary>
        /// Get long from dr.
        /// </summary>
        /// <param name="dr">The data reader instance (Read() already been called).</param>
        /// <param name="columnName">The column name.</param>
        /// <param name="valueOnError">on null return this value</param>
        /// <returns></returns>
        public static long DrGetInt64(SqlDataReader dr, string columnName, long valueOnError)
        {
            int index = dr.GetOrdinal(columnName);
            if (dr.IsDBNull(index))
            {
                return valueOnError;
            }
            return dr.GetInt64(index);
        }

        /// <summary>
        /// Get datetime from dr.
        /// </summary>
        /// <param name="dr">The data reader instance (Read() already been called).</param>
        /// <param name="columnName">The column name.</param>
        /// <param name="valueOnError">on null return this value</param>
        /// <returns></returns>
        public static DateTime DrGetDateTime(SqlDataReader dr, string columnName, DateTime valueOnError)
        {
            int index = dr.GetOrdinal(columnName);
            if (dr.IsDBNull(index))
            {
                return valueOnError;
            }
            return dr.GetDateTime(index);
        }

        /// <summary>
        /// Get datetime from dr.
        /// </summary>
        /// <param name="dr">The data reader instance (Read() already been called).</param>
        /// <param name="columnName">The column name.</param>
        /// <param name="valueOnError">The value to use when the data is null.</param>
        /// <param name="dateFormat">The date format.</param>
        /// <returns></returns>
        public static string DrGetDateTime(SqlDataReader dr, string columnName, DateTime valueOnError, string dateFormat)
        {
            DateTime dt = DrGetDateTime(dr, columnName, valueOnError);
            return dt.ToString(dateFormat);
        }

        /// <summary>
        /// Get boolean from dr.
        /// </summary>
        /// <param name="dr">The data reader instance (Read() already been called).</param>
        /// <param name="columnName">The column name.</param>
        /// <param name="valueOnError">The value to use when the data is null.</param>
        /// <returns></returns>
        public static bool DrGetBoolean(SqlDataReader dr, string columnName, bool valueOnError)
        {
            int index = dr.GetOrdinal(columnName);
            if (dr.IsDBNull(index))
            {
                return valueOnError;
            }
            return dr.GetBoolean(index);
        }

        /// <summary>
        /// Extract value from dr and then compare, and return base on the expression
        /// </summary>
        /// <param name="dr">The data reader instance (Read() already been called).</param>
        /// <param name="columnName">The column name.</param>
        /// <param name="compareValue">The compare value.</param>
        /// <param name="trueValue">The true value.</param>
        /// <param name="falseValue">The false value.</param>
        /// <returns></returns>
        public static string DrStringIIF(SqlDataReader dr, string columnName, string compareValue, string trueValue, string falseValue)
        {
            string value = DrGetString(dr, columnName, "");

            if (string.Compare(value, compareValue, true) == 0)
            {
                return trueValue;
            }

            return falseValue;
        }

        /// <summary>
        /// Return a list of the columnName name in the datareader.
        /// </summary>
        /// <param name="dr">The data reader instance.</param>
        /// <returns></returns>
        public static List<string> DrGetColumnList(SqlDataReader dr)
        {
            DataTable dt = dr.GetSchemaTable();
            List<string> list = new List<string>();

            foreach (DataRow data in dt.Rows)
            {
                list.Add(data["ColumnName"].ToString());
            }

            return list;
        }

        /// <summary>
        /// Check to see if the dr has a columnName called "Column"
        /// </summary>
        /// <param name="dr">The data reader instance.</param>
        /// <param name="columnName">The column name.</param>
        /// <returns></returns>
        public static bool DrContainsColumn(SqlDataReader dr, string columnName)
        {
            List<string> columnList = DrGetColumnList(dr);
            return columnList.Contains(columnName);
        }

        /// <summary>
        /// Generate JSON object for the entire result and close the dr once finished.
        /// </summary>
        /// <param name="dr">The dr.</param>
        /// <param name="primaryColumns">The primary columns.</param>
        /// <returns></returns>
        public static string DrToJson(SqlDataReader dr, params string[] primaryColumns)
        {
            return DrToJson(dr, false, primaryColumns);
        }

        /// <summary>
        /// Generate JSON object for the entire result and close the dr once finished.
        /// </summary>
        /// <param name="dr">The dr.</param>
        /// <param name="debugLayout">if set to <c>true</c> the JSON object will be layout with better readability.</param>
        /// <param name="primaryColumns">The Key to the data rows.</param>
        /// <returns></returns>
        public static string DrToJson(SqlDataReader dr, bool debugLayout, params string[] primaryColumns)
        {
            try
            {
                if (dr == null)
                {
                    return "{}";
                }

                bool hasPrimaryCol = (primaryColumns.Length > 0);

                string pkStringTemplate = "pk";
                if (hasPrimaryCol)
                {
                    for (int i = 0; i < primaryColumns.Length; i++)
                    {
                        pkStringTemplate += "${" + i + "}";
                    }
                }
                List<string> columnNames = DrGetColumnList(dr);

                string debugTab = (debugLayout ? "\t" : "");
                string debugNewLine = (debugLayout ? "\n" : "");
                string debugSpace = (debugLayout ? " " : "");

                string rows = "{" + debugNewLine;
                string curRow = "";
                int rowNumber = 0;

                while (dr.Read())
                {
                    if (hasPrimaryCol)
                    {
                        string pk = string.Format(pkStringTemplate, DrGetRowList(dr, primaryColumns).ToArray());
                        curRow = debugTab + "\"" + pk + "\"" + debugSpace + ":" + debugSpace + "{" + debugNewLine;
                    }
                    else
                    {
                        curRow = debugTab + "\"" + rowNumber + "\"" + debugSpace + ":" + debugSpace + "{" + debugNewLine;
                        rowNumber++;
                    }

                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        curRow += debugTab + debugTab + "\"" + JTool.StringEscape(columnNames[i]) + "\"" + debugSpace +
                                  ":" + debugSpace + "\"" + JTool.StringEscape(dr[i].ToString()) + "\"," + debugNewLine;
                    }
                    curRow = curRow.TrimEnd(',', '\n');
                    curRow += debugNewLine + debugTab + "}," + debugNewLine;

                    rows += curRow;
                }

                rows = rows.TrimEnd(',', '\n');
                rows += debugNewLine + "}";

                DisposeDr(dr);

                return rows;
            }
            catch (Exception)
            {
                return "{}";
            }
        }

        /// <summary>
        /// Get a list of data based on a list of column names 
        /// (ie. convert ONE row with selected columns into an array).
        /// </summary>
        /// <param name="dr"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public static List<string> DrGetRowList(SqlDataReader dr, params string[] columns)
        {
            List<string> list = new List<string>(columns.Length);
            foreach (string col in columns)
            {
                list.Add(dr[col].ToString());
            }
            return list;
        }
        #endregion

        #region [ Static DB Tools Functions: SQLBuild & SQLStatic]
        /// <summary>
        /// Helps build a dynamic sql statement without having to 
        /// add the parameters one by one.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters (First value will correspond to the first variable, etc.).</param>
        /// <returns></returns>
        public static SqlCommand SqlBuildCommand(string sql, params object[] parameters)
        {
            SqlCommand cmd = new SqlCommand(sql);
            int index = 0;

            MatchCollection mc = Regex.Matches(sql, "@\\w+");
            if (mc.Count != parameters.Length)
            {
                throw new Exception(
                    "Number of parameters and number of values doesn't match, please check your sql statement and your paramters.");
            }

            List<string> addedParameters = new List<string>();
            foreach (Match m in mc)
            {
                if (!addedParameters.Contains(m.Value))
                {
                    object value = parameters[index] == null ? DBNull.Value : parameters[index];
                    cmd.Parameters.AddWithValue(m.Value, value);
                    addedParameters.Add(m.Value);
                }
                index++;
            }

            return cmd;
        }

        /// <summary>
        /// Helps build a dynamic Stored Procedure sql statement without having to 
        /// add the parameters one by one.
        /// </summary>
        /// <param name="sql">The SQL. Example:  SQLBuildSPCommand("usp_Login(@UserName, @Password, @Result OUTPUT)", txtUserName.Text, txtPassword.Text, null);
        /// Syntax: StoreProcedureName (@ArgName [Input|InputOutput|Output|ReturnValue])
        /// </param>
        /// <param name="parameters">The parameters value.</param>
        /// <returns></returns>
        public static SqlCommand SqlBuildSpCommand(string sql, params object[] parameters)
        {
            //(?<atd>.*?)
            Match queryData = Regex.Match(sql, @"(?<SPName>\w+)\s*\((?<Parameters>.+)\)", RegexOptions.Singleline);
            if (!queryData.Success)
            {
                throw new Exception("Invalid syntax. Syntax: StoreProcedureName (@ArgName [Input|InputOutput|Output|ReturnValue])");
            }

            string SPName = queryData.Groups["SPName"].Value.Trim();
            string parameterList = queryData.Groups["Parameters"].Value.Trim();

            SqlCommand cmd = new SqlCommand(SPName);
            cmd.CommandType = CommandType.StoredProcedure;
            int index = 0;

            MatchCollection mc = Regex.Matches(parameterList, @"(?<ParamName>@\w+)(?<Direction>\s(Input|InputOutput|Output|ReturnValue))?");
            if (mc.Count != parameters.Length)
            {
                throw new Exception(
                    "Number of parameters and number of values doesn't match, please check your sql statement and your paramters.");
            }

            List<string> addedParameters = new List<string>();
            foreach (Match m in mc)
            {
                if (!addedParameters.Contains(m.Value))
                {
                    string paramName = m.Groups["ParamName"].Value;
                    string direction = m.Groups["Direction"].Success ? m.Groups["Direction"].Value.Trim() : "Input";
                    ParameterDirection paramDirection;
                    try {
                        paramDirection = (ParameterDirection)Enum.Parse(typeof(ParameterDirection), direction, true);
                    }
                    catch{
                        throw new Exception("Invalid parameter's direction. Parameter: " + paramName + ", Direction: " + direction + ". "
                               + "Direction must be Input, InputOutput, Output or ReturnValue. Syntax: StoreProcedureName (@ArgName [Input|InputOutput|Output|ReturnValue])"); 
                    }

                    object value = parameters[index] == null ? DBNull.Value : parameters[index];
                    cmd.Parameters.AddWithValue(paramName, value).Direction = paramDirection;
                    addedParameters.Add(paramName);
                }
                index++;
            }

            return cmd;
        }

        /// <summary>
        /// Build the sql "IN" expression (include bracket, but no "IN" keyword)
        /// </summary>
        /// <param name="set">array of data (string, int, long)</param>
        /// <returns>return a string -> ("a", "b", "c")</returns>
        public static string SqlBuildInCondition<T> (IList<T> set)
        {            
            StringBuilder sb = new StringBuilder(" (");
            
            if (typeof(T) == typeof(string)){
                foreach (object s in set)
                {
                    sb.Append("'");
                    sb.Append(SqlEscapeString(s.ToString()));
                    sb.Append("', ");
                }

                if (sb.Length > 5)
                {
                    sb.Remove(sb.Length - 2, 2);
                }
                sb.Append(")");
            }
            else if (typeof(T) == typeof(int) || typeof(T) == typeof(long))
            {
                List<string> stringSet = Arr.ToString((IList)set);
                string list = Arr.Implode(", ", stringSet);

                return " (" + list + ")";
            }

            return sb.ToString();
        }

        /// <summary>
        /// Convert DateTime instance to a string to be use later in a sql statement (121 format)      
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        /// <remarks>
        /// select convert(char, getdate(), 100) --mon dd yyyy hh:mmAM (or PM)
        /// select convert(char, getdate(), 101) --mm/dd/yyyy
        /// select convert(char, getdate(), 102) --yyyy.mm.dd
        /// select convert(char, getdate(), 103) --dd/mm/yyyy
        /// select convert(char, getdate(), 104) --dd.mm.yyyy
        /// select convert(char, getdate(), 105) --dd-mm-yyyy
        /// select convert(char, getdate(), 106) --dd mon yyyy
        /// select convert(char, getdate(), 107) --mon dd, yyyy
        /// select convert(char, getdate(), 108) --hh:mm:ss
        /// select convert(char, getdate(), 109) --mon dd yyyy hh:mm:ss:mmmAM (or PM)
        /// select convert(char, getdate(), 110) --mm-dd-yyyy
        /// select convert(char, getdate(), 111) --yyyy/mm/dd
        /// select convert(char, getdate(), 112) --yyyymmdd
        /// select convert(char, getdate(), 113) --dd mon yyyy hh:mm:ss:mmm
        /// select convert(char, getdate(), 114) --hh:mm:ss:mmm(24h)
        /// select convert(char, getdate(), 120) --yyyy-mm-dd hh:mm:ss(24h)
        /// select convert(char, getdate(), 121) --yyyy-mm-dd hh:mm:ss.mmm
        /// </remarks>
        public static string SqlFormatDateTime(DateTime dt)
        {
            return dt.ToString("yyyy-MM-dd HH:mm:ss.fff");
        }

        /// <summary>
        /// Return date string in sql recognizable format (yyyy-MM-dd)
        /// </summary>
        /// <param name="dt">The date and time.</param>
        /// <returns></returns>
        public static string SqlFormatDateOnly(DateTime dt)
        {
            return dt.ToString("yyyy-MM-dd");
        }

        /// <summary>
        /// Return the "Between" sql query string.
        /// </summary>
        /// <param name="from">From date.</param>
        /// <param name="to">To date.</param>
        /// <returns>" BETWEEN '2000-02-11 12:24:30.032' AND '2000-07-11 18:0:30.772' "</returns>
        public static string SqlBetweenStatement(DateTime from, DateTime to)
        {
            return " BETWEEN '" + SqlFormatDateTime(from) + "' AND '" + SqlFormatDateTime(to) + "' ";
        }

        /// <summary>
        /// Return the "Between" sql statement. Set "From" to midnight and "To" to the next day at 11:59pm
        /// </summary>
        /// <param name="from"></param>
        /// <param name="to"></param>
        /// <returns></returns>
        public static string SqlBetweenStartAndEndDay(DateTime from, DateTime to)
        {
            return " BETWEEN '" + SqlFormatDateOnly(from) + " 00:00:00.000' AND '" + SqlFormatDateOnly(to) + " 23:59:59.999' ";
        }

        /// <summary>
        /// Return the "Between" sql string (use greater and lesser sign instead of the between keyword)
        /// </summary>
        /// <param name="columnName">Name of the column.</param>
        /// <param name="from">From date.</param>
        /// <param name="to">To date.</param>
        /// <returns></returns>
        public static string SqlBetweenOperator(string columnName, DateTime from, DateTime to)
        {
            return string.Format(" {0} >= '{1}' AND {0} <= '{2}' ", columnName, SqlFormatDateTime(from), SqlFormatDateTime(to));
        }

        /// <summary>
        /// Return the "Between" sql statement. Set "From" to midnight and "To" to the next day at 11:59:59pm (NOT using the between keyword)
        /// </summary>
        /// <param name="columnName">Name of the column.</param>
        /// <param name="from">From date. Will reset the time to zero hour.</param>
        /// <param name="to">To Date. Will change the time to 23:59:59.999</param>
        /// <returns></returns>
        public static string SqlBetweenOperatorStartAndEndDay(string columnName, DateTime from, DateTime to)
        {
            return string.Format(" {0} >= '{1} 00:00:00.000' AND {0} <= '{2} 23:59:59.999' ", columnName, SqlFormatDateOnly(from), SqlFormatDateOnly(to));
        }

        /// <summary>
        /// Escape all special character in an sql query.
        /// </summary>
        /// <param name="sql">The SQL value text.</param>
        /// <returns></returns>
        public static string SqlEscapeString(string sql)
        {
            sql = sql.Replace("'", @"''");
            return sql;
        }
        #endregion        

        #region [ Database Functions: CreateDBConnection ]
        /// <summary>
        /// Create a connection to the database
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="tries">Number of attempt to connect so far</param>
        /// <returns></returns>
        private SqlConnection createDbConnection(string connectionString, int tries)
        {
            _lastException = null;
            _lastErrorType = ErrorType.NoError;

            try
            {
                SqlConnection connection = new SqlConnection(connectionString);
                try
                {
                    connection.Open();
                    if (connection.State == ConnectionState.Open)
                    {
                        return connection;
                    }
                    return null;
                }
                catch (SqlException xpt)
                {
                    foreach (SqlError error in xpt.Errors)
                    {
                        // Database not found or not accessible
                        if (error.Number == 18452)
                        {
                            handleError(xpt, ErrorType.ConnectionError);
                            return null;
                        }
                    }

                    try
                    {
                        SqlConnection.ClearPool(connection);                        
                    }
                    catch (Exception) { }

                    if (tries < 3)
                    {
                        return createDbConnection(connectionString, tries + 1);
                    }
                    handleError(xpt, ErrorType.ConnectionError);
                    return null;
                }                
            }
            catch (Exception xpt)
            {
                handleError(xpt, ErrorType.ConnectionError);
                return null;
            }
        }

        /// <summary>
        /// Create connection using the class property connection string
        /// </summary>
        /// <returns></returns>
        public SqlConnection CreateConnection()
        {
            return createDbConnection(ConnectionString, 0);
        }

        /// <summary>
        /// Create a connection to the database
        /// </summary>
        /// <param name="userConnectionString">The user connection string.</param>
        /// <returns></returns>
        public SqlConnection CreateConnection(string userConnectionString)
        {
            return createDbConnection(userConnectionString, 0);
        }
        #endregion

        #region [ Event & Delegates ]
        /// <summary>
        /// When an error occured.
        /// </summary>
        public delegate void DbErrorOccurredDelegate(QuickDb quickDB, DbErrorEventArgs e);        

        /// <summary>
        /// When encounter an error this event will fire.
        /// </summary>
        public event DbErrorOccurredDelegate DbErrorOccurred;
        #endregion

        #region [ Event & Delegates Private Function Calls ]
        /// <summary>
        /// When DB error occurred.
        /// </summary>
        /// <param name="exception">The exception.</param>
        /// <param name="errorType">Type of the error.</param>
        /// <returns>True if the event is not null</returns>
        protected virtual bool onDbErrorOccurred(Exception exception, ErrorType errorType)
        {
            try
            {
                return Dlgt.ThreadSafeInvoke(DbErrorOccurred, this, new DbErrorEventArgs(exception, errorType));
            }
            catch (Exception xpt)
            {
                handleError(xpt, ErrorType.LibraryError);
            }
            return false;
        }
        #endregion

        #region [ Connection Checking Functions ]
        /// <summary>
        /// Determines whether the sql connection can be created.
        /// </summary>
        /// <returns>
        /// 	<c>true</c> if the sql connection can be created; otherwise, <c>false</c>.
        /// </returns>
        public bool CanCreateConnection()
        {
            using (SqlConnection conn = CreateConnection())
            {
                if (conn != null)
                {
                    return true;
                }
            }
            return false;
        }
        #endregion

        #region [ List Query ]
        /// <summary>
        /// Gets the table list in the current database (view is not included).
        /// </summary>
        /// <returns></returns>
        public List<string> GetTableList()
        {
            const string getTableQuery = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE'";
            return ListQuery<string>(getTableQuery, "TABLE_NAME");
        }

        /// <summary>
        /// Gets the name of the columns in the specified table.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <returns></returns>
        public List<string> GetColumnName(string tableName)
        {
            const string getColumnQuery = "SELECT name FROM SYSCOLUMNS WHERE  ID = OBJECT_ID(@TableName)";
            SqlCommand cmd = new SqlCommand(getColumnQuery);
            cmd.Parameters.AddWithValue("@TableName", tableName);

            return ListQuery<string>(cmd, "name");
        }

        /// <summary>
        /// Execute the query and build a list from the result. Convert all the rows of ONE column into a list.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlCommand">The SQL command.</param>
        /// <param name="columnName">Name of the column.</param>
        /// <returns></returns>
        public List<T> ListQuery<T>(string sqlCommand, string columnName)
        {
            return ListQuery<T>(new SqlCommand(sqlCommand), columnName);
        }

        /// <summary>
        /// Execute the query and build a list from the result. Convert all the rows of ONE column into a list.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cmd">The CMD.</param>
        /// <param name="columnName">Name of the column.</param>
        /// <returns></returns>
        public List<T> ListQuery<T>(SqlCommand cmd, string columnName)
        {
            List<T> list = new List<T>();
            try
            {
                using (SqlDataReader dr = Query(cmd))
                {
                    if (dr != null)
                    {
                        while (dr.Read())
                        {
                            list.Add((T)Convert.ChangeType(dr[columnName], typeof(T)));
                        }
                    }
                }
            }
            catch (Exception xpt)
            {
                handleError(xpt, ErrorType.QueryError);
            }
            return list;
        }
        #endregion

        #region [ Short Query ]
        /// <summary>
        /// Check to see if the query has any result.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public bool DynExists(string sql, params object[] parameters)
        {
            using (SqlDataReader dr = DynQuery(sql, parameters))
            {
                if (dr != null && dr.HasRows)
                {
                    return true;
                }
                return false;
            }
        }   
        #endregion

        #region [ Maintainance ]
        /// <summary>
        /// Truncate and shrink the log dir for the current database (from connection string) to specified size.
        /// </summary>
        /// <param name="sizeInMb">The size in mb.</param>
        public void AsyncTruncateLog(int sizeInMb)
        {
            try
            {
                ThreadPool.QueueUserWorkItem(o => TruncateLog(sizeInMb));
            }
            catch (Exception xpt)
            {
                handleError(xpt, ErrorType.LibraryError);
            }
        }

        /// <summary>
        /// Truncate and shrink the log dir for the current database (from connection string) to specified size (timeout in 1 minute).
        /// </summary>
        /// <param name="sizeInMb">The size in mb.</param>
        public bool TruncateLog(int sizeInMb)
        {
            return TruncateLog(sizeInMb, 3600);
        }

        /// <summary>
        /// Truncate and shrink the log dir for the current database (from connection string) to specified size.
        /// </summary>
        /// <param name="sizeInMb">The size in mb.</param>
        /// <param name="timeout">The timeout (in millisecond second).</param>
        /// <returns></returns>
        public bool TruncateLog(int sizeInMb, int timeout)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    if (string.IsNullOrEmpty(conn.Database))
                    {
                        return false;
                    }

                    string query = "BACKUP LOG [" + conn.Database + "] WITH NO_LOG";
                    int result = NonQuery(query, timeout);
                    if (result == INT_NonQueryError)
                    {
                        return false;
                    }

                    query = "DBCC SHRINKDATABASE ([" + conn.Database + "], " + sizeInMb + ")";
                    result = NonQuery(query, timeout);
                    if (result == INT_NonQueryError)
                    {
                        return false;
                    }

                    return true;
                }
            }
            catch (Exception xpt)
            {
                handleError(xpt, ErrorType.LibraryError);
                return false;
            }
        }
        #endregion


        #region [ Query Functions ]
        /// <summary>
        /// Execute the SqlCommand and return the DataReader.
        /// The connection automatically closes, when the DataReader is closed.
        /// </summary>
        /// <param name="sql">The sql command.</param>
        /// <returns>Null on error.</returns>
        public SqlDataReader Query(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql);
            return Query(cmd);
        }

        /// <summary>
        /// Execute the SqlCommand and return the DataReader.
        /// The connection automatically closes, when the DataReader is closed.
        /// </summary>
        /// <param name="cmd">The CMD.</param>
        /// <returns>
        /// Null on error.
        /// </returns>
        public SqlDataReader Query(SqlCommand cmd)
        {
            SqlConnection connection = CreateConnection();
            if (connection == null)
            {
                return null;
            }

            try
            {
                cmd.Connection = connection;
                cmd.CommandTimeout = CommandTimeout;

                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return dr;
            }
            catch (Exception xpt)
            {
                handleError(xpt, ErrorType.QueryError);
                DisposeDbConnection(connection);
                return null;
            }
        }

        /// <summary>
        /// Use external connection. If the connection is opened use it, else create new connect
        /// and assign to the ref. You MUST close the connection yourself.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <param name="connection">The connection.</param>
        /// <returns></returns>
        public SqlDataReader Query(string sql, SqlConnection connection)
        {
            SqlCommand cmd = new SqlCommand(sql);
            SqlDataReader dr = Query(cmd, connection);
            return dr;
        }

        /// <summary>
        /// Use external connection. If the connection is opened use it, else create new connect
        /// and assign to the ref. You MUST close the connection yourself.
        /// </summary>
        /// <param name="cmd">The command.</param>
        /// <param name="connection">The connection.</param>
        /// <returns></returns>
        public SqlDataReader Query(SqlCommand cmd, SqlConnection connection)
        {
            if (connection == null || connection.State != ConnectionState.Open)
            {
                connection = CreateConnection();
            }

            if (connection == null)
            {
                return null;
            }

            try
            {
                cmd.Connection = connection;
                cmd.CommandTimeout = CommandTimeout;

                SqlDataReader dr = cmd.ExecuteReader();
                return dr;
            }
            catch (Exception xpt)
            {
                handleError(xpt, ErrorType.QueryError);
                DisposeDbConnection(connection);
                return null;
            }
        }

        /// <summary>
        /// Queries the transaction.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <param name="connection">The connection.</param>
        /// <param name="transaction">The transaction.</param>
        /// <returns></returns>
        public SqlDataReader QueryTransaction(string sql, SqlConnection connection, SqlTransaction transaction)
        {
            SqlCommand cmd = new SqlCommand(sql, connection, transaction);
            return Query(cmd, connection);
        }
        #endregion

        #region [ Dynamic Query ]
        /// <summary>
        /// Create the sqlcommand on the fly and execute the query.
        /// </summary>
        /// <param name="sql">The SQL (Ex: DynQuery("SELECT * From UserTable WHERE UserID=@UserID AND Status=@Status", 10, "Active"))</param>
        /// <param name="parameters">The parameters (First value will correspond to the first variable, etc.).</param>
        /// <returns></returns>
        /// <example>DynQuery("SELECT * From UserTable WHERE UserID=@UserID AND Status=@Status", 10, "Active")</example>
        public SqlDataReader DynQuery(string sql, params object[] parameters)
        {
            SqlCommand cmd = SqlBuildCommand(sql, parameters);
            return Query(cmd);
        }
        #endregion

        #region [ Scalar Query ]
        /// <summary>
        /// Create the SqlCommand, execute the query and return a single value.
        /// </summary>
        /// <typeparam name="T">Return DataType.</typeparam>
        /// <param name="sql">The SQL command.</param>
        /// <param name="columnName">Name of the column to extract the data.</param>
        /// <param name="valueOnError">The value to return on error.</param>
        /// <returns></returns>
        public T ScalarQuery<T>(string sql, string columnName, T valueOnError)
        {
            SqlCommand cmd = new SqlCommand(sql);
            return ScalarQuery<T>(cmd, columnName, valueOnError);
        }

        /// <summary>
        /// Create the SqlCommand, execute the query and return a single value.
        /// </summary>
        /// <typeparam name="T">Return DataType.</typeparam>
        /// <param name="sql">The SQL command.</param>
        /// <param name="columnIndex">Index of the column.</param>
        /// <param name="valueOnError">The value to return on error.</param>
        /// <returns></returns>
        public T ScalarQuery<T>(string sql, int columnIndex, T valueOnError)
        {
            SqlCommand cmd = new SqlCommand(sql);
            return ScalarQuery<T>(cmd, columnIndex, valueOnError);
        }

        /// <summary>
        /// Execute the query and return a single value.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cmd">The SQL command.</param>
        /// <param name="columnName">Name of the column.</param>
        /// <param name="valueOnError">The value to return on error.</param>
        /// <returns></returns>
        public T ScalarQuery<T>(SqlCommand cmd, string columnName, T valueOnError)
        {
            return ScalarQueryHelper<T>(cmd, -1, columnName, valueOnError);
        }

        /// <summary>
        /// Execute the query and return a single value.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cmd">The SQL command.</param>
        /// <param name="columnIndex">Index of the column.</param>
        /// <param name="valueOnError">The value to return on error.</param>
        /// <returns></returns>
        public T ScalarQuery<T>(SqlCommand cmd, int columnIndex, T valueOnError)
        {
            return ScalarQueryHelper<T>(cmd, columnIndex, null, valueOnError);
        }

        private T ScalarQueryHelper<T>(SqlCommand cmd, int columnIndex, string columnName, T valueOnError)
        {
            using (SqlConnection connection = CreateConnection())
            {
                if (connection == null)
                {
                    return valueOnError;
                }

                try
                {
                    cmd.Connection = connection;
                    cmd.CommandTimeout = CommandTimeout;

                    using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.CloseConnection))
                    {
                        if (dr != null && dr.Read())
                        {
                            T result;
                            if (columnIndex != -1)
                            {
                                result = (T)Convert.ChangeType(dr[columnIndex], typeof(T));
                            }
                            else {
                                result = (T)Convert.ChangeType(dr[columnName], typeof(T));
                            }
                            return result;
                        }
                        return valueOnError;
                    }
                }
                catch (Exception xpt)
                {
                    handleError(xpt, ErrorType.QueryError);
                    return valueOnError;
                }
            }
        }
        #endregion

        #region [ Dynamic Scalar Query ]
        /// <summary>
        /// Create the sqlcommand on the fly, execute the query and return the one column value.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="columnName">Name of the column.</param>
        /// <param name="valueOnError">The value to return on error.</param>
        /// <param name="sql">The SQL (Ex: DynScalarQuery("SELECT * From UserTable WHERE UserID=@UserID AND Status=@Status", 10, "Active"))</param>
        /// <param name="parameters">The parameters (First value will correspond to the first variable, second value for the second variable etc.).</param>
        /// <returns></returns>
        public T DynScalarQuery<T>(string columnName, T valueOnError, string sql, params object[] parameters)
        {
            SqlCommand cmd = SqlBuildCommand(sql, parameters);
            return ScalarQuery<T>(cmd, columnName, valueOnError);
        }

        /// <summary>
        /// Create the sqlcommand on the fly, execute the query and return the one column value.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="columnIndex">Index of the column.</param>
        /// <param name="valueOnError">The value to return on error.</param>
        /// <param name="sql">The SQL (Ex: DynScalarQuery("SELECT * From UserTable WHERE UserID=@UserID AND Status=@Status", 10, "Active"))</param>
        /// <param name="parameters">The parameters (First value will correspond to the first variable, second value for the second variable etc.).</param>
        /// <returns></returns>
        public T DynScalarQuery<T>(int columnIndex, T valueOnError, string sql, params object[] parameters)
        {
            SqlCommand cmd = SqlBuildCommand(sql, parameters);
            return ScalarQuery<T>(cmd, columnIndex, valueOnError);
        }
        #endregion

        #region [ NonQuery ]
        /// <summary>
        /// Execute the SqlCommand and return number of affected rows.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <returns>
        /// NON_QUERY_ERROR constant on error.
        /// </returns>
        public int NonQuery(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql);
            return NonQuery(cmd);
        }

        /// <summary>
        /// Execute the SqlCommand and return number of affected rows.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <param name="timeout">The timeout in seconds.</param>
        /// <returns></returns>
        public int NonQuery(string sql, int timeout)
        {
            SqlCommand cmd = new SqlCommand(sql);
            return NonQuery(cmd, timeout);
        }

        /// <summary>
        /// Execute the SqlCommand and return number of affected rows.
        /// </summary>
        /// <param name="cmd">The CMD.</param>
        /// <returns>NON_QUERY_ERROR constant on error.</returns>
        public int NonQuery(SqlCommand cmd)
        {
            return NonQuery(cmd, CommandTimeout);
        }

        /// <summary>
        /// Execute the SqlCommand and return number of affected rows.
        /// </summary>
        /// <param name="cmd">The CMD.</param>
        /// <param name="timeout">The timeout in seconds.</param>
        /// <returns></returns>
        public int NonQuery(SqlCommand cmd, int timeout)
        {
            using (SqlConnection connection = CreateConnection())
            {
                if (connection == null)
                {
                    return INT_NonQueryError;
                }

                try
                {
                    cmd.Connection = connection;
                    cmd.CommandTimeout = timeout;

                    int affectedRows = cmd.ExecuteNonQuery();
                    return affectedRows;
                }
                catch (Exception xpt)
                {
                    handleError(xpt, ErrorType.QueryError);
                    return INT_NonQueryError;
                }
            }
        }

        /// <summary>
        /// Use external connection. If the connection is opened use it, else create new connect
        /// and assign to the ref. You MUST close the connection yourself.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <param name="connection">The connection.</param>
        /// <returns></returns>
        public int NonQuery(string sql, SqlConnection connection)
        {
            SqlCommand cmd = new SqlCommand(sql);
            int result = NonQuery(cmd, connection);
            return result;
        }

        /// <summary>
        /// Use external connection. If the connection is opened use it, else create new connect
        /// and assign to the ref. You MUST close the connection yourself.
        /// </summary>
        /// <param name="cmd">The command.</param>
        /// <param name="connection">The connection.</param>
        /// <returns></returns>
        public int NonQuery(SqlCommand cmd, SqlConnection connection)
        {
            if (connection == null || connection.State != ConnectionState.Open)
            {
                connection = CreateConnection();
            }

            if (connection == null)
            {
                return INT_NonQueryError;
            }

            try
            {
                cmd.Connection = connection;
                cmd.CommandTimeout = CommandTimeout;

                int result = cmd.ExecuteNonQuery();
                return result;
            }
            catch (Exception xpt)
            {
                handleError(xpt, ErrorType.QueryError);
                DisposeDbConnection(connection);
                return INT_NonQueryError;
            }
        }

        /// <summary>
        /// Nons the query transaction.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <param name="connection">The connection.</param>
        /// <param name="transaction">The transaction.</param>
        /// <returns></returns>
        public int NonQueryTransaction(string sql, SqlConnection connection, SqlTransaction transaction)
        {
            ////Todo: need use case and verification
            SqlCommand cmd = new SqlCommand(sql, connection, transaction);
            return NonQuery(cmd, connection);
        }
        #endregion

        #region [ DynNonQuery ]
        /// <summary>
        /// Create the sqlcommand on the fly and execute the query.
        /// </summary>
        /// <param name="sql">The SQL (Ex: DynNonQuery("UPDATE UserTable SET UserName=@UserName WHERE UserID=@UserID", "Tan Much", 35))</param>
        /// <param name="parameters">The parameters (First value will correspond to the first variable, etc.).</param>
        /// <returns></returns>
        /// <example>DynNonQuery("UPDATE UserTable SET UserName=@UserName WHERE UserID=@UserID", "Tan Much", 35)</example>
        public int DynNonQuery(string sql, params object[] parameters)
        {
            SqlCommand cmd = SqlBuildCommand(sql, parameters);
            return NonQuery(cmd);
        }
        #endregion


        #region [ SPQuery ]
        /// <summary>
        /// Execute the stored procedure and return the data reader object.
        /// </summary>
        /// <param name="cmd">The CMD.</param>
        /// <returns>
        /// Return NULL on error
        /// </returns>
        public SqlDataReader SpQuery(SqlCommand cmd)
        {
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr = Query(cmd);
            return dr;
        }        
        #endregion

        #region [ DynSpQuery ]
        /// <summary>
        /// Build the stored procedure command and execute it.
        /// </summary>
        /// <param name="cmd">The CMD.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public SqlDataReader DynSpQuery(SqlCommand cmd, params object[] parameters)
        {
            cmd = SqlBuildSpCommand(cmd.CommandText, parameters);
            return Query(cmd);
        }
        #endregion

        #region [ SpNonQuery ]
        /// <summary>
        /// Execute the stored procedure.
        /// </summary>
        /// <param name="cmd">The CMD.</param>
        /// <returns></returns>
        public int SpNonQuery(SqlCommand cmd)
        {
            cmd.CommandType = CommandType.StoredProcedure;
            int rowAffected = NonQuery(cmd);
            return rowAffected;
        }
        #endregion

        #region [ DynSpNonQuery ]
        /// <summary>
        /// Dyns the Stored Procedure query.
        /// </summary>
        /// <param name="cmd">The CMD.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns>
        /// Return null on error.
        /// </returns>
        public SqlCommand DynSpNonQuery(SqlCommand cmd, params object[] parameters)
        {
            cmd = SqlBuildSpCommand(cmd.CommandText, parameters);
            if (NonQuery(cmd) == INT_NonQueryError)
            {
                return null;
            }
            return cmd;
        }
        #endregion

        #region [ SPNonQueryOut ]
        /// <summary>
        /// Execute Stored Procedure and extract the output parameter value.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cmd">The CMD.</param>
        /// <param name="outParamName">Name of the output parameter, includes the @ sign (eg. "@ParameterName").</param>
        /// <param name="valueOnError">The default value.</param>
        /// <returns></returns>
        public T SpNonQueryOut<T>(SqlCommand cmd, string outParamName, T valueOnError)
        {
            try
            {
                cmd.Parameters[outParamName].Direction = ParameterDirection.Output;
                int rowAffected = SpNonQuery(cmd);

                if (rowAffected == INT_NonQueryError)
                {
                    return valueOnError;
                }

                return (T)Convert.ChangeType(cmd.Parameters[outParamName], typeof(T));
            }
            catch (Exception xpt)
            {
                handleError(xpt, ErrorType.InputError);
                return valueOnError;
            }
        }
        #endregion
        

        #region [ Insert Query Functions ]
        /// <summary>
        /// Excecute the Insert command and return the last inserted id.
        /// </summary>
        /// <param name="cmd">The CMD.</param>
        /// <returns></returns>
        public long Insert(SqlCommand cmd)
        {
            cmd.CommandText += "; SELECT SCOPE_IDENTITY() AS [ID]";
            long id = ScalarQuery<long>(cmd, "ID", INT_NonQueryError);
            return id;
        }

        /// <summary>
        /// Excecute the Insert command and return the last inserted id.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns>return NON_QUERY_ERROR on error</returns>
        public long DynInsert(string sql, params object[] parameters)
        {
            SqlCommand cmd = SqlBuildCommand(sql, parameters);
            return Insert(cmd);
        }

        /// <summary>
        /// Get last inserted auto increment id (return NON_QUERY_ERROR on error).
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <returns>
        /// Return NON_QUERY_ERROR on error.
        /// </returns>
        public long GetLastInsertedId(string tableName)
        {
            string sql = "SELECT IDENT_CURRENT(@TableName) AS LastInsertedID";
            long id = DynScalarQuery<long>("LastInsertedID", INT_NonQueryError, sql, tableName);
            return id;
        }
        #endregion 

        #region [ Dataset Related Functions ]
        /// <summary>
        /// Execute the query and return the dataset (return NULL on error).
        /// </summary>
        /// <param name="cmd">The CMD.</param>
        /// <returns></returns>
        public DataSet GetDataset(SqlCommand cmd)
        {
            using (SqlConnection connection = CreateConnection())
            {
                if (connection == null)
                {
                    return null;
                }

                try
                {
                    cmd.Connection = connection;
                    DataSet ds = new DataSet();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);

                    da.Fill(ds);
                    connection.Close();

                    return ds;
                }
                catch (Exception xpt)
                {
                    handleError(xpt, ErrorType.QueryError);
                    return null;
                }
            }
        }
        /// <summary>
        /// Execute the query and return the dataset (return NULL on error).
        /// </summary>
        /// <param name="sql">The SQL statement.</param>
        /// <returns></returns>
        public DataSet GetDataset(string sql)
        {
            return GetDataset(new SqlCommand(sql));
        }
        #endregion

        #region [ Bulk Query ]
        /// <summary>
        /// Inserts data in bulk.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="columnList">"(column1Name, column2Name, column3Name) include the bracket"</param>
        /// <param name="data">object[] { row1_object[], row2_object[], row3_object[]}</param>
        /// <returns></returns>
        public int BulkInsert(string tableName, string columnList, IList<object> data)
        {
            return BulkInsert(tableName, columnList, data, CommandTimeout);
        } 

        /// <summary>
        /// Execute a bulk insert command using an array of objects (Return NON_QUERY_ERROR on error).
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="columnList">"(column1Name, column2Name, column3Name) include the bracket"</param>
        /// <param name="data">object[] { row1_object[], row2_object[], row3_object[]}</param>
        /// <param name="timeout">The timeout.</param>
        /// <returns>Return NON_QUERY_ERROR on error</returns>
        public int BulkInsert(string tableName, string columnList, IList<object> data, int timeout)
        {
            if (data == null || data.Count == 0)
            {
                return INT_NonQueryError;
            }

            columnList = columnList.Trim();
            if (!columnList.StartsWith("(") || !columnList.EndsWith(")"))
            {
                throw new Exception("ColumnList must be in this format: '(col1, col2, col3, ..., coln )'. No quote and must have the outer brackets.");
            }

            try
            {
                string sqlQuery = prepareBulkInsert(tableName, columnList, data);

                if (sqlQuery == null)
                {
                    return INT_NonQueryError;
                }

                SqlCommand cmd = new SqlCommand(sqlQuery);
                cmd.CommandTimeout = timeout;
                int affected = NonQuery(cmd);
                return affected;
            }
            catch (Exception ex)
            {
                handleError(ex, ErrorType.QueryError);
                return INT_NonQueryError;
            }
        }

        /// <summary>
        /// Bulks the insert transaction.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="columnList">The column list.</param>
        /// <param name="data">The data.</param>
        /// <returns></returns>
        public int BulkInsertTransaction(string tableName, string columnList, IList<object> data)
        {
            return BulkInsertTransaction(tableName, columnList, data, CommandTimeout);
        }

        /// <summary>
        /// Bulks the insert transaction.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="columnList">The column list.</param>
        /// <param name="data">The data.</param>
        /// <param name="timeout">The timeout.</param>
        /// <returns></returns>
        public int BulkInsertTransaction(string tableName, string columnList, IList<object> data, int timeout)
        {
            if (data == null || data.Count == 0)
            {
                return INT_NonQueryError;
            }

            columnList = columnList.Trim();
            if (!columnList.StartsWith("(") || !columnList.EndsWith(")"))
            {
                throw new Exception("ColumnList must be in this format: '(col1, col2, col3, ..., coln )'. No quote and must have the outer brackets.");
            }

            try
            {
                string sqlQuery = prepareBulkInsert(tableName, columnList, data);

                if (sqlQuery == null)
                {
                    return INT_NonQueryError;
                }
                
                using (SqlConnection connection = CreateConnection())
                {
                    using (SqlTransaction transaction = connection.BeginTransaction())
                    {
                        SqlCommand cmd = new SqlCommand(sqlQuery, connection, transaction);
                        cmd.CommandTimeout = timeout;

                        int result = NonQuery(cmd, connection);
                        if (result == INT_NonQueryError)
                        {
                            transaction.Rollback();
                            return INT_NonQueryError;
                        }

                        transaction.Commit();
                        return result;
                    }
                }
            }
            catch (Exception ex)
            {
                handleError(ex, ErrorType.QueryError);
                return INT_NonQueryError;
            }
        }
       
        /// <summary>
        /// Prepare the string for the InsertBulk function
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="columnList">The columnName list.</param>
        /// <param name="data">The data.</param>
        /// <returns></returns>
        private string prepareBulkInsert(string tableName, string columnList, IList<object> data)
        {
            try
            {
                //Regex hex = new Regex(@"^0x[a-fA-F0-9]+$", RegexOptions.Compiled);

                string sqlQuery = string.Format("INSERT INTO {0} {1} ", tableName, columnList);
                StringBuilder sb = new StringBuilder();

                for (int i = 0; i < data.Count; i++)
                {
                    object[] olist = (object[])data[i];

                    sb.Append("\n\tSELECT ");
                    foreach (object o in olist)
                    {
                        if (o is string)
                        {
                            string s = (string)o;
                            if (Str.TrimEqual(s, "getdate()"))
                            {
                                sb.Append("GETDATE(), ");
                            }

                            #region [ Removed ]
                            // Use actual null value instead
                            //else if (Str.TrimEqual(s, "null"))
                            //{
                            //    sb.Append("NULL, ");
                            //}

                            // This might cause a unintensional conversion, just force the user to convert "0xff" to 255 instead
                            //else if (hex.IsMatch(s))      
                            //{

                            //    sb.Append(s + ", ");
                            //}
                            #endregion

                            else
                            {
                                s = SqlEscapeString(s);
                                sb.Append("'" + s + "', ");
                            }
                        }
                        else if (o is sbyte || o is byte)
                        {
                            int integer = Convert.ToInt32(o);
                            sb.Append(integer + ", ");
                        }
                        else if (o is short || o is ushort || o is int || o is uint
                               || o is long || o is ulong || o is float || o is double || o is decimal)
                        {
                            sb.Append(o + ", ");
                        }
                        else if (o is DateTime)
                        {
                            DateTime dt = (DateTime)o;
                            sb.Append("'" + SqlFormatDateTime(dt) + "', ");
                        }
                        else if (o is bool)
                        {
                            bool b = (bool)o;
                            string s = b ? "1" : "0";
                            sb.Append(s + ", ");
                        }
                        else if (o == null)
                        {
                            sb.Append("NULL, ");
                        }
                        else
                        {
                            throw new Exception("Invalid datatype, row data can only contains ('getdate()', null, string, number, date, boolean)");
                        }
                    }
                    sb.Remove(sb.Length - 2, 2);
                }
                sb.Replace("'", "''");
                sb.Insert(0, sqlQuery + "\nEXEC ('");
                sb.Append("\n')");

                return sb.ToString();
            }
            catch (Exception ex)
            {
                handleError(ex, ErrorType.InputError);
                return null;
            }
        }
        #endregion


        private void sample()
        { 
            // You do not need to open or close the connection
            // the library will do that for you
            // The only time you need to close something is 
            // when you receive a SqlDataReader
            QuickDb db = new QuickDb("Data Source=local;Initial Catalog=SampleDb;Integrated Security=SSPI;");

                       
            // Using datareader
            using (SqlDataReader dr = db.Query("SELECT * FROM Users"))
            {
                // Use the dr here                
            }

            
            // Get single value from a row using column index
            int count = db.ScalarQuery("SELECT COUNT(Name) FROM Users", columnIndex: 0, valueOnError: -1);
            int sameCount = db.ScalarQuery("SELECT COUNT(Name) FROM Users", 0, -1);

            
            // You can cast your result using generic query (specifying "<DateTime>" is optional)
            DateTime lastUpdated = db.ScalarQuery<DateTime>("SELECT LastUpdated FROM Users WHERE id = 10", 
                        columnName: "LastUpdated", valueOnError: DateTime.MinValue);


            // Scalar query using sqlcomand, sometime using string query is unsafe
            SqlCommand cmdPhone = new SqlCommand("SELECT Phone FROM Users WHERE id = @id");
            cmdPhone.Parameters.AddWithValue("@id", 75);
            string phone = db.ScalarQuery(cmdPhone, "Phone", "No Phone");

            
            // Update using sql query
            int result = db.NonQuery("UPDATE Users SET Name='John Stevenson' WHERE id=10");
            // Error
            if (result == QuickDb.INT_NonQueryError)
            {   // Error message
                string error = db.LastExeception.Message;
            }


            // These are the most interesting functions and I use them most often
            // DynQuery, DynNonQuery, DynScalarQuery. The library dynamicly create
            // SqlCommand and assign the value to them 
            // (the value params[] is the same order as parameter appearances)
            // You get the security of SqlCommand without 
            // the hassle of building your own paramenters
            using (SqlDataReader dr = db.DynQuery("SELECT * FROM Users WHERE Age > @age AND Birthday > @bday", 18, new DateTime(2000, 1, 1)))
            {
                while (dr.Read())
                {
                    Console.WriteLine(dr["Name"].ToString());
                }
            }

            // DynScalar: get the first column, return -10 if an error occur
            int age = db.DynScalarQuery<int>(columnIndex: 0, valueOnError: -10, 
                        sql: "SELECT Age FROM Users WHERE Id = @id", parameters: 75);
            int ageShorterLessLegible  = db.DynScalarQuery(0, -10, 
                        "SELECT Age FROM Users WHERE Id = @id", 75);

            // You can specify date as DateTime or a string literal, 
            // in this case it a formatted sqldate string style 121
            db.DynNonQuery("UPDATE Users SET Name=@name, Birthday=@bday WHERE Id=@id", 
                    "Elvis Presley", "1935-01-08", 135);

            

            // Trucate db log 
            db.AsyncTruncateLog(sizeInMb: 10);


            // Get sql server utc datetime
            DateTime dbUtcTime = db.GetDbDateTimeUtc();
        }

    } // END QuickDB Class
}


/*
      System.Data.Common..::.DataAdapter
      System.Data.Common..::.DbCommand
      System.Data.Common..::.DbCommandBuilder
      System.Data.Common..::.DbConnection
      System.Data.Common..::.DbDataReader
*/